package avicit.im.lantop.basic.libstructuredefine.action.service.impl;

import avicit.cbb.techclass.tabledefine.commtablemanager.domain.CbbTabColumns;
import avicit.cbb.techclass.tabledefine.commtablemanager.domain.CbbTabNode;
import avicit.cbb.techclass.tabledefine.commtablemanager.domain.CbbTables;
import avicit.im.lantop.basic.archiveclassmanager.domain.LantopArchiveClass;
import avicit.im.lantop.basic.libstructuredefine.cbbtabexceptioncolumns.dao.CbbTabExceptionColumnsDao;
import avicit.im.lantop.basic.libstructuredefine.cbbtabexceptioncolumns.dto.CbbTabExceptionColumnsDTO;
import avicit.platform6.commons.utils.PojoUtil;
import avicit.platform6.core.dao.hibernate.CommonHibernateDao2;
import avicit.platform6.core.properties.PlatformConstant;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @author emorabu
 * @version 1.0
 * @date 2020/11/23
 */
@Repository
public class LantopArchiveClassDao2 {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    private CommonHibernateDao2 hibernateDao;
    private static final Logger LOGGER = LoggerFactory.getLogger(LantopArchiveClassDao2.class);
    @Autowired
    CbbTabExceptionColumnsDao cbbTabExceptionColumnsDao;

    public List<LantopArchiveClass> getAllArchiveClassInLibraryStructure(List<String> validOrgIds){
        StringBuilder sql = new StringBuilder();
        sql.append("select id,\n");
        sql.append("       org_id,\n");
        sql.append("       dept_id,\n");
        sql.append("       sys_id,\n");
        sql.append("       archive_class_node_code,\n");
        sql.append("       archive_class_node_name,\n");
        sql.append("       parent_id,\n");
        sql.append("       archive_type,\n");
        //   sql.append("       archive_class_node_type,\n");
        //   sql.append("       is_visible,\n");
        sql.append("       node_sort_number,\n");
        //   sql.append("       relat_workflow,\n");
        sql.append("       table_layer,\n");
        //  sql.append("       archive_class_node_path,\n");
        sql.append("       remark,\n");
        sql.append("       attribute_01 \n");
        sql.append("from lantop_archive_class lac \n");
        sql.append("where LAC.ARCHIVE_CLASS_NODE_TYPE='' OR LAC.ARCHIVE_CLASS_NODE_TYPE IS NULL AND LAC.ARCHIVE_CLASS_NODE_CODE <> 'LANTOP_PUB_FIELD' ");
        sql.append("and lac.org_id in (:validOrgIds)");
        //    sql.append("and lac.parent_id!='ROOT' ");
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("validOrgIds", validOrgIds);
        List<LantopArchiveClass> archiveClasses = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(LantopArchiveClass.class));
//        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql.toString());
        return archiveClasses;
//        return result;
    }

    public LantopArchiveClass getLantopArchiveClassById(String id){
        return hibernateDao.get(LantopArchiveClass.class, id);
    }

    /**
     * 节点向上向下遍历
     * @param id
     * @return
     */
    public List<LantopArchiveClass> getRecursiveLantopArchiveClassById(String id){
        StringBuilder sql = new StringBuilder();
        sql.append("with t as(\n");
        sql.append("select id,\n");
        sql.append("       org_id,\n");
        sql.append("       dept_id,\n");
        sql.append("       sys_id,\n");
        sql.append("       archive_class_node_code,\n");
        sql.append("       archive_class_node_name,\n");
        sql.append("       parent_id,\n");
        sql.append("       archive_type,\n");
        //   sql.append("       archive_class_node_type,\n");
        //   sql.append("       is_visible,\n");
        sql.append("       node_sort_number,\n");
        //   sql.append("       relat_workflow,\n");
        sql.append("       table_layer,\n");
        //  sql.append("       archive_class_node_path,\n");
        sql.append("       remark,\n");
        sql.append("       attribute_01 \n");
        sql.append("from lantop_archive_class lac \n");
        sql.append("where LAC.ARCHIVE_CLASS_NODE_TYPE='' OR LAC.ARCHIVE_CLASS_NODE_TYPE IS NULL AND LAC.ARCHIVE_CLASS_NODE_CODE <> 'LANTOP_PUB_FIELD' ");
        sql.append(")\n");
        sql.append("SELECT t.archive_class_node_name,t.org_id, t.parent_id, t.* FROM t\n");
        sql.append("connect by prior t.parent_id = t.id\n");
        sql.append("start with t.id = ?\n");
        sql.append("union all\n");
        sql.append("SELECT t.archive_class_node_name,t.org_id, t.parent_id, t.* FROM t\n");
        sql.append("where t.id != ? \n");
        sql.append("connect by prior t.id = t.parent_id\n");
        sql.append("start with t.id = ?");

//        List<Map<String, Object>> archiveClasses = jdbcTemplate.queryForList(sql.toString(), new Object[]{id, id, id});
        List<LantopArchiveClass> archiveClasses = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LantopArchiveClass.class), new Object[]{id, id, id});
        return archiveClasses;
    }

    public void saveLantopArchiveClass(LantopArchiveClass lantopArchiveClass){
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.id, t1.archive_class_node_name\n");
        sql.append("from lantop_archive_class t1\n");
        sql.append("where t1.archive_class_node_code = ?\n");
        sql.append("and t1.org_id = ? \n");
        sql.append("and t1.archive_type = ?");

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), lantopArchiveClass.getArchiveClassNodeCode(),
                lantopArchiveClass.getOrgId(), lantopArchiveClass.getArchiveType());
        if(list.isEmpty()){
            PojoUtil.setSysProperties(lantopArchiveClass, PlatformConstant.OpType.insert);
            hibernateDao.save(lantopArchiveClass );
            return;
        }
        if (list.size() == 1){
            Map<String, Object> item = list.get(0);
            LantopArchiveClass lantopArchiveClassDest = getLantopArchiveClassById((String) item.get("ID"));
            lantopArchiveClassDest.setArchiveClassNodeName(lantopArchiveClass.getArchiveClassNodeName());
            PojoUtil.setSysProperties(lantopArchiveClassDest, PlatformConstant.OpType.update);
            hibernateDao.update(lantopArchiveClassDest );
            return;
        }

        LOGGER.info("duplicate archive_class_node_code:"+lantopArchiveClass.getArchiveClassNodeCode()+" in org_id:"+lantopArchiveClass.getOrgId());
    }

    public void saveCbbTables(CbbTables cbbTables){
        PojoUtil.setSysProperties(cbbTables, PlatformConstant.OpType.insert);
        hibernateDao.save(cbbTables );
    }

    public void saveCbbTabNode(CbbTabNode cbbTabNode){
        PojoUtil.setSysProperties(cbbTabNode, PlatformConstant.OpType.insert);
        hibernateDao.save(cbbTabNode );
    }

    public void saveCbbTabColumns(CbbTabColumns cbbTabColumns){
        PojoUtil.setSysProperties(cbbTabColumns, PlatformConstant.OpType.insert);
        hibernateDao.save(cbbTabColumns );
    }

    /**
     * 获取 LantopArchiveClass 节点下的所有 cbb_tables 节点的父 LantopArchiveClass 列表
     * @return
     */
    public List<Map<String, Object>> getCbbTablesAndParentArchiveClass(String nodeId){
        StringBuilder sql = new StringBuilder();
        sql.append("select T2.ID,\n");
        sql.append("       T2.org_id,\n");
        sql.append("       T2.archive_class_node_code,\n");
        sql.append("       T2.archive_class_node_name,\n");
        sql.append("       T2.archive_type,\n");
        sql.append("       T2.archive_class_node_type,\n");
        sql.append("       t1.id                      sub_id,\n");
        sql.append("       t1.ARCHIVE_CLASS_NODE_NAME sub_ARCHIVE_CLASS_NODE_NAME,\n");
        sql.append("       t1.TABLE_NAME              sub_TABLE_NAME\n");
        sql.append("  from (select t.id, t.PARENT_ID, t.ARCHIVE_CLASS_NODE_NAME, t.TABLE_NAME\n");
        sql.append("          from LANTOP_ARCHIVE_TAB_CLASS_V t\n");
        sql.append("         where t.TABLE_NAME != '-1'\n");
        sql.append("        connect by prior t.id = t.parent_id\n");
        sql.append("         start with t.id = ?) t1\n");
        sql.append("  JOIN lantop_archive_class t2\n");
        sql.append("    on t2.id = t1.parent_id");
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), new Object[]{nodeId});
        return list;
    }

    public List<CbbTables> getCbbTablesByIds(Set<String> ids){
        StringBuilder sql = new StringBuilder();
        sql.append("select * from cbb_tables s1\n");
        sql.append("where s1.id in (:ids)\n");
        sql.append("order by instr(:idsStr,s1.id)");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("ids", ids);
        params.put("idsStr", StringUtils.collectionToCommaDelimitedString(ids));
        List<CbbTables> cbbTablesList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(CbbTables.class));
        return cbbTablesList;
    }

    public CbbTables getCbbTablesById(String id){
        return hibernateDao.get(CbbTables.class, id);
    }

    public List<CbbTabNode> getCbbTabNodesByTableIds(Set<String> ids){
        StringBuilder sql = new StringBuilder();
        sql.append("select * from cbb_tab_node s1\n");
        sql.append("where s1.TABLE_ID in (:ids)\n");
        sql.append("order by instr(:idsStr,s1.TABLE_ID)");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("ids", ids);
        params.put("idsStr", StringUtils.collectionToCommaDelimitedString(ids));
        List<CbbTabNode> cbbTabNodeList = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(CbbTabNode.class));
        return cbbTabNodeList;
    }

    /**
     * 查询指定的组织集合中哪些有特定的CbbTables
     * @param cbbTables
     * @param orgIds
     * @param archiveClassNodeCode
     * @return
     */
    public List<String> getOrgIdsByCbbTables(CbbTables cbbTables, List<String> orgIds, String archiveClassNodeCode){
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.org_id from cbb_tables t1\n");
        sql.append("where (\n");

        String tableName = cbbTables.getTableName();
        final String tableTitle = cbbTables.getTableTitle();
        String prefix = "LANTOP_"+archiveClassNodeCode+"_";  // LANTOP_JC_
        String suffix = tableName.substring(tableName.lastIndexOf("_")+1); // AJJ
        for (String orgId : orgIds) {
            sql.append("( t1.table_title = '"+tableTitle+"' and t1.org_id = '"+orgId+"' \n");
            sql.append("and ( REGEXP_LIKE(t1.table_name, '"+(prefix+suffix)+"') or  REGEXP_LIKE(t1.table_name, '"+prefix+"\\d+_"+suffix+"')  ) ) \n");
            sql.append("or \n");
        }
        String sqlStr = sql.substring(0, sql.length()-4);
        sql = new StringBuilder(sqlStr);
        sql.append(")");
        sql.append("and exists (\n");
        sql.append("select 1 from lantop_archive_class t2\n");
        sql.append("where t2.id in (\n");
        sql.append("            select t3.node_id from cbb_tab_node t3 where t3.table_id = t1.id\n");
        sql.append("      )\n");
        sql.append(")");

        List<String> validOrgIds = jdbcTemplate.queryForList(sql.toString(), String.class);
        return validOrgIds;
    }

    public LantopArchiveClass getArchiveClassByTableId(String cbbTableId){
        StringBuilder sql = new StringBuilder();
        sql.append("select * from lantop_archive_class t2\n");
        sql.append("where t2.id in (\n");
        sql.append("    select t1.node_id from cbb_tab_node t1\n");
        sql.append("    where t1.table_id = '"+cbbTableId+"'\n");
        sql.append("    and ( t2.archive_class_node_type<>'2' or  t2.archive_class_node_type is null) \n");
        sql.append(")");
        List<LantopArchiveClass> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LantopArchiveClass.class));
        if(CollectionUtils.isEmpty(result)){
            return null;
        }
        return result.get(0);
    }

    public List<CbbTables> getCbbTablesListInOtherOrg(CbbTables cbbTables, List<String> orgIds){
        if(cbbTables == null || CollectionUtils.isEmpty(orgIds)){
            return new ArrayList<>();
        }
        String tableName = cbbTables.getTableName();
        LantopArchiveClass archiveClass = getArchiveClassByTableId(cbbTables.getId());
        String prefix = "LANTOP_"+archiveClass.getArchiveClassNodeCode()+"_";  // LANTOP_JC_
        String suffix = tableName.substring(tableName.lastIndexOf("_")+1); // AJJ

        StringBuilder sql = new StringBuilder();
        sql.append("select t1.id, t1.org_id, t1.table_name from cbb_tables t1\n");
        sql.append("where t1.table_title = :tableTitle\n");
        sql.append("and ( REGEXP_LIKE(t1.table_name, '"+(prefix+suffix)+"') or  REGEXP_LIKE(t1.table_name, '"+prefix+"\\d+_"+suffix+"')  )  \n");
        sql.append("and t1.org_id in (:orgIds)");

        final HashMap<String, Object> param = new HashMap<>();
//        param.put("tableName", cbbTables.getTableName());
        param.put("tableTitle", cbbTables.getTableTitle());
        param.put("orgIds", orgIds);

        final NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        final List<CbbTables> list = namedParameterJdbcTemplate.query(sql.toString(), param,new BeanPropertyRowMapper<>(CbbTables.class));
        return list;
    }

    public List<CbbTabColumns> getCbbTabColumnsByTableId(String tableId){
        StringBuilder sql = new StringBuilder();
        sql.append("select id,\n");
        sql.append("       table_id,\n");
        sql.append("       col_name,\n");
        sql.append("       col_label,\n");
        sql.append("       col_type,\n");
        sql.append("       col_length,\n");
        sql.append("       col_decimal,\n");
        sql.append("       col_is_sys,\n");
        sql.append("       col_order,\n");
        sql.append("       col_is_must,\n");
        sql.append("       col_is_visible,\n");
        sql.append("       col_is_search,\n");
        sql.append("       col_is_edit,\n");
        sql.append("       col_is_tab_visible,\n");
        sql.append("       col_is_detail,\n");
        sql.append("       col_dropdown_type,\n");
        sql.append("       col_gene_method,\n");
        sql.append("       col_rule_name,\n");
        sql.append("       col_rule_title,\n");
        sql.append("       custom_path,\n");
        sql.append("       remark,\n");
        sql.append("       org_id,\n");
        sql.append("       dept_id,\n");
        sql.append("       sys_id,\n");
        sql.append("       secret_level,\n");
        sql.append("       sys_application_id,\n");
        sql.append("       attribute_01,\n");
        sql.append("       attribute_02,\n");
        sql.append("       attribute_03,\n");
        sql.append("       attribute_04,\n");
        sql.append("       attribute_05,\n");
        sql.append("       attribute_06,\n");
        sql.append("       attribute_07,\n");
        sql.append("       attribute_08,\n");
        sql.append("       attribute_09,\n");
        sql.append("       attribute_10,\n");
        sql.append("       attribute_11,\n");
        sql.append("       attribute_12,\n");
        sql.append("       attribute_13,\n");
        sql.append("       attribute_14,\n");
        sql.append("       attribute_15,\n");
        sql.append("       col_show_format,\n");
        sql.append("       col_gene_method_rule,\n");
        sql.append("       col_is_display,\n");
        sql.append("       col_is_complement,\n");
        sql.append("       col_complement_num,\n");
        sql.append("       col_gene_method_rule_text\n");
        sql.append("  from cbb_tab_columns t\n");
        sql.append("  where t.table_id = ?");

//        List<CbbTabColumns> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbbTabColumns.class), new Object[]{tableId});
        List<CbbTabColumns> list = hibernateDao.getSession().createCriteria(CbbTabColumns.class).add(Restrictions.eq("tableId", tableId)).list();
        return list;
    }

    public Map<String, Map<String, Object>> getCbbTabColumnsMapByTableId(String tableId){
        StringBuilder sql = new StringBuilder();
        sql.append("select id,\n");
        sql.append("       table_id,\n");
        sql.append("       col_name,\n");
        sql.append("       col_label,\n");
        sql.append("       col_type,\n");
        sql.append("       col_length,\n");
        sql.append("       col_decimal,\n");
        sql.append("       col_is_sys,\n");
        sql.append("       col_order,\n");
        sql.append("       col_is_must,\n");
        sql.append("       col_is_visible,\n");
        sql.append("       col_is_search,\n");
        sql.append("       col_is_edit,\n");
        sql.append("       col_is_tab_visible,\n");
        sql.append("       col_is_detail,\n");
        sql.append("       col_dropdown_type,\n");
        sql.append("       col_gene_method,\n");
        sql.append("       col_rule_name,\n");
        sql.append("       col_rule_title,\n");
        sql.append("       custom_path,\n");
        sql.append("       remark,\n");
        sql.append("       org_id,\n");
        sql.append("       dept_id,\n");
        sql.append("       sys_id,\n");
        sql.append("       secret_level,\n");
        sql.append("       sys_application_id,\n");
        sql.append("       last_update_date,\n");
        sql.append("       last_updated_by,\n");
        sql.append("       creation_date,\n");
        sql.append("       created_by,\n");
        sql.append("       last_update_ip,\n");
        sql.append("       version,\n");
        sql.append("       attribute_01,\n");
        sql.append("       attribute_02,\n");
        sql.append("       attribute_03,\n");
        sql.append("       attribute_04,\n");
        sql.append("       attribute_05,\n");
        sql.append("       attribute_06,\n");
        sql.append("       attribute_07,\n");
        sql.append("       attribute_08,\n");
        sql.append("       attribute_09,\n");
        sql.append("       attribute_10,\n");
        sql.append("       attribute_11,\n");
        sql.append("       attribute_12,\n");
        sql.append("       attribute_13,\n");
        sql.append("       attribute_14,\n");
        sql.append("       attribute_15,\n");
        sql.append("       col_show_format,\n");
        sql.append("       col_gene_method_rule,\n");
        sql.append("       col_is_display,\n");
        sql.append("       col_is_complement,\n");
        sql.append("       col_complement_num,\n");
        sql.append("       col_gene_method_rule_text\n");
        sql.append("  from cbb_tab_columns t");
        sql.append("  where t.table_id = ?");

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), new Object[]{tableId});
        Map<String,Map<String, Object>> result = new HashMap<>(list.size());
        for (Map<String, Object> map : list) {
            result.put((String) map.get("COL_NAME"), map); // COL_NAME 是字段英文名
        }
        return result;
    }

    public void saveCbbTabExceptionColumns(List<CbbTabExceptionColumnsDTO> cbbTabExceptionColumnsDTOList) throws Exception{
        for (CbbTabExceptionColumnsDTO cbbTabExceptionColumnsDTO : cbbTabExceptionColumnsDTOList) {
            PojoUtil.setSysProperties(cbbTabExceptionColumnsDTO, PlatformConstant.OpType.insert);
            hibernateDao.save(cbbTabExceptionColumnsDTO );
        }
    }

    public void saveCbbTabColumnsList(List<CbbTabColumns> cbbTabColumns){
        Session session = hibernateDao.openSession();
        Transaction tx = session.beginTransaction();
        for (int i = 0; i < cbbTabColumns.size()-1; i++) {
            CbbTabColumns bean = cbbTabColumns.get(i);
            PojoUtil.setSysProperties(bean, PlatformConstant.OpType.insert);
            session.save(bean);
            if (i % 20 == 0) {
                session.flush();
                session.clear();
            }
        }
        //提交事务
        tx.commit();
        //关闭事务
        session.close();
    }

    public int getNextNumOfCbbTableName(String tableName, String archiveClassCode){
        String suffix = tableName.substring(tableName.lastIndexOf("_"));// 后缀, _AJJ
        String prefix = "LANTOP_"+archiveClassCode+"_";

        StringBuilder sql = new StringBuilder();
        sql.append("select max (substr(table_name,1,length(table_name)-1)) from ( \n");
        sql.append("select substr(t3.table_name, "+(prefix.length()+1)+", instr(t3.table_name,'_',-1)-"+prefix.length()+"  ) table_name\n");
        sql.append("from cbb_tables t3\n");
        sql.append("where  REGEXP_LIKE(t3.table_name, '"+prefix+"\\d+"+suffix+"')\n");
        sql.append("union all\n");
        sql.append("select '0_' table_name from dual\n");
        sql.append(")");

        Integer num1 =  jdbcTemplate.queryForObject(sql.toString(), Integer.class);

        sql = new StringBuilder();
        sql.append("select max (substr(table_name,1,length(table_name)-1)) from ( \n");
        sql.append("select substr(t3.table_name, "+(prefix.length()+1)+", instr(t3.table_name,'_',-1)-"+prefix.length()+"  ) table_name\n");
        sql.append("from user_tables t3\n");
        sql.append("where  REGEXP_LIKE(t3.table_name, '"+prefix+"\\d+"+suffix+"')\n");
        sql.append("union all\n");
        sql.append("select '0_' table_name from dual\n");
        sql.append(")");

        Integer num2 =  jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return (num1 > num2 ? num1 : num2)+1;
    }

    // 建表
    public void createPhysicalTable(CbbTables cbbTables, List<CbbTabColumns> cbbTabColumnsList) throws Exception {
        StringBuilder sql = new StringBuilder();
        //create
        String tableName = cbbTables.getTableName();
        sql.append("create table ").append(tableName).append(" ( \n");
        for (CbbTabColumns cbbTabColumns : cbbTabColumnsList) {
            sql.append(cbbTabColumns.getColName()).append("\t").append(getFieldType(cbbTabColumns));
            if("ID".equals(cbbTabColumns.getColName())){
                sql.append("not null");
            }
            sql.append(",\t");
        }
        try {
            jdbcTemplate.execute(sql.substring(0,sql.length()-2)+")");
        }catch (Exception e){
            e.printStackTrace();
        }
        // 注释 comment on table CBB_TAB_COLUMNS is '列信息';
        sql = new StringBuilder();
        sql.append("comment on table ").append(tableName).append(" is '").append(cbbTables.getTableTitle()).append("'");
        try {
            jdbcTemplate.execute(sql.toString());
        }catch (Exception e){
            e.printStackTrace();
        }
        // 字段注释
        setFieldCommentToPhysicalTable(cbbTabColumnsList, tableName);
        // 主键索引 alter table CBB_TAB_EXCEPTION_COLUMNS add constraint PK_CBB_TAB_EXCEPTION_COLUMNS primary key (ID);
        sql  = new StringBuilder();
        sql.append("alter table ").append(tableName).append(" add constraint PK_").append(tableName).append("  primary key (ID)");
        try {
            jdbcTemplate.execute(sql.toString());
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void setFieldCommentToPhysicalTable(List<CbbTabColumns> cbbTabColumnsList, String tableName) {
        StringBuilder sql;// 字段注释 comment on column CBB_TAB_EXCEPTION_COLUMNS.id is '主键';
        for (CbbTabColumns cbbTabColumns : cbbTabColumnsList) {
            sql = new StringBuilder();
            sql.append("comment on column ").append(tableName).append(".").append(cbbTabColumns.getColName()).append(" is '").append(cbbTabColumns.getColLabel()).append("'");
            try{
                jdbcTemplate.execute(sql.toString());
            }catch (Exception e){
                e.printStackTrace();
                LOGGER.info(e.getMessage());
            }
        }
    }

    /**TODO 增加字段同时增加注释
        alter table tempdb add (
     *             age integer  not null,
     *             has_money number(9,2)
     *         );
     * @param cbbTables
     * @param cbbTabColumnsList
     * @throws Exception
     */
    public void addFieldToPhysicalTable(CbbTables cbbTables, List<CbbTabColumns> cbbTabColumnsList) {
        StringBuilder sql;
        for (CbbTabColumns cbbTabColumns : cbbTabColumnsList) {
            sql = new StringBuilder();
            sql.append("alter table ").append(cbbTables.getTableName()).append(" add ( \t");
            try{
                sql.append(cbbTabColumns.getColName()).append("\t").append(getFieldType(cbbTabColumns)).append(")");
                jdbcTemplate.execute(sql.toString());
            }catch (Exception e){
                e.printStackTrace();
                LOGGER.info(e.getMessage());
            }
        }

        setFieldCommentToPhysicalTable(cbbTabColumnsList, cbbTables.getTableName());
    }

    /** // TODO 删除字段
     * alter table tempdb drop (age, has_money)
     * @param cbbTables
     * @param cbbTabColumnsList
     */
    public void deleteFieldInPhysicalTable(CbbTables cbbTables, List<Map<String, Object>> cbbTabColumnsList){
        StringBuilder sql = new StringBuilder();
        sql.append("alter table ").append(cbbTables.getTableName()).append(" drop ( ");
        for (Map<String, Object> cbbTabColumnsMap : cbbTabColumnsList) {
            sql.append(cbbTabColumnsMap.get("COL_NAME")).append(",");
        }
        try {
            jdbcTemplate.execute(sql.substring(0, sql.length()-1)+")");
        }catch (Exception e){
            LOGGER.info(e.getMessage());
            e.printStackTrace();
        }
    }

    // TODO 修改字段(类型, 名称)

    /**
     *  alter table tempdb modify ( age number(10) )
     * @param cbbTables
     * @param cbbTabColumnsList
     */
    public void modifyFieldInPhysicalTable(CbbTables cbbTables, List<CbbTabColumns> cbbTabColumnsList){
        StringBuilder sql;
        for (CbbTabColumns cbbTabColumns : cbbTabColumnsList) {
            try {
                sql = new StringBuilder();
                sql.append("alter table ").append(cbbTables.getTableName()).append(" modify (").append(cbbTabColumns.getColName()).append("\t").append(getFieldType(cbbTabColumns)).append(")");
                jdbcTemplate.execute(sql.toString());
            }catch (Exception e){
                e.printStackTrace();
                LOGGER.info(e.getMessage());
            }
        }

        // 注释 comment on column tempdb.age is '年龄1';

        setFieldCommentToPhysicalTable(cbbTabColumnsList, cbbTables.getTableName());
    }

    public String getFieldType(CbbTabColumns cbbTabColumns) throws Exception {
//        if("ID".equals(cbbTabColumns.get))
        switch (cbbTabColumns.getColType()){
            case "string":
                return "varchar2("+cbbTabColumns.getColLength()+")";
            case "integer":
                return "number("+cbbTabColumns.getColLength()+")";
            case "float":
                return "number("+cbbTabColumns.getColLength()+","+cbbTabColumns.getColDecimal()+")";
            case "date":
                return "date";
            case "blob":
                return "blob";
            case "clob":
                return "clob";
            default:
                throw new Exception("no column type");
        }

    }
    public void deleteCbbTabColumnsList(List<String> ids){
        StringBuilder sql = new StringBuilder();
        sql.append("delete from cbb_tab_columns\n");
        sql.append("where id in (:ids)");

        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("ids", ids);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        namedParameterJdbcTemplate.update(sql.toString(), paramMap);
    }

    public boolean isColumnDataEmpty(String tableName, String colName, String colType){
        StringBuilder sql = new StringBuilder();
        // 该字段有值的记录数为 0, 则所有记录的这个字段都没值
        sql.append("select count("+colName+")\n");
        sql.append("from "+tableName+" \n");
        if("string".equals(colType)){
            sql.append("where trim("+colName+") is not null");
        }else if("integer".equals(colType) || "float".equals(colType)){
            sql.append("where "+colName+" != null or "+colName+"!=0");
        }else {
            sql.append("where "+colName+" != null");
        }
        try {
            Integer num = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
            return num == 0;
        }catch (Exception e){
            e.printStackTrace();
            LOGGER.info(e.getMessage());
        }
        return true;
    }

    public void updateCbbTabColumnsList(List<Map<String,Object>> cbbTabColumnsList){
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE CBB_TAB_COLUMNS\n");
        sql.append("   SET ID = :ID,\n");
        sql.append("       TABLE_ID = :TABLE_ID,\n");
        sql.append("       COL_NAME = :COL_NAME,\n");
        sql.append("       COL_LABEL = :COL_LABEL,\n");
        sql.append("       COL_TYPE = :COL_TYPE,\n");
        sql.append("       COL_LENGTH = :COL_LENGTH,\n");
        sql.append("       COL_DECIMAL = :COL_DECIMAL,\n");
        sql.append("       COL_IS_SYS = :COL_IS_SYS,\n");
        sql.append("       COL_ORDER = :COL_ORDER,\n");
        sql.append("       COL_IS_MUST = :COL_IS_MUST,\n");
        sql.append("       COL_IS_VISIBLE = :COL_IS_VISIBLE,\n");
        sql.append("       COL_IS_SEARCH = :COL_IS_SEARCH,\n");
        sql.append("       COL_IS_EDIT = :COL_IS_EDIT,\n");
        sql.append("       COL_IS_TAB_VISIBLE = :COL_IS_TAB_VISIBLE,\n");
        sql.append("       COL_IS_DETAIL = :COL_IS_DETAIL,\n");
        sql.append("       COL_DROPDOWN_TYPE = :COL_DROPDOWN_TYPE,\n");
        sql.append("       COL_GENE_METHOD = :COL_GENE_METHOD,\n");
        sql.append("       COL_RULE_NAME = :COL_RULE_NAME,\n");
        sql.append("       COL_RULE_TITLE = :COL_RULE_TITLE,\n");
        sql.append("       CUSTOM_PATH = :CUSTOM_PATH,\n");
        sql.append("       REMARK = :REMARK,\n");
        sql.append("       ORG_ID = :ORG_ID,\n");
        sql.append("       DEPT_ID = :DEPT_ID,\n");
        sql.append("       SYS_ID = :SYS_ID,\n");
        sql.append("       SECRET_LEVEL = :SECRET_LEVEL,\n");
        sql.append("       SYS_APPLICATION_ID = :SYS_APPLICATION_ID,\n");
        sql.append("       LAST_UPDATE_DATE = :LAST_UPDATE_DATE,\n");
        sql.append("       LAST_UPDATED_BY = :LAST_UPDATED_BY,\n");
        sql.append("       CREATION_DATE = :CREATION_DATE,\n");
        sql.append("       CREATED_BY = :CREATED_BY,\n");
        sql.append("       LAST_UPDATE_IP = :LAST_UPDATE_IP,\n");
        sql.append("       VERSION = :VERSION,\n");
        sql.append("       ATTRIBUTE_01 = :ATTRIBUTE_01,\n");
        sql.append("       ATTRIBUTE_02 = :ATTRIBUTE_02,\n");
        sql.append("       ATTRIBUTE_03 = :ATTRIBUTE_03,\n");
        sql.append("       ATTRIBUTE_04 = :ATTRIBUTE_04,\n");
        sql.append("       ATTRIBUTE_05 = :ATTRIBUTE_05,\n");
        sql.append("       ATTRIBUTE_06 = :ATTRIBUTE_06,\n");
        sql.append("       ATTRIBUTE_07 = :ATTRIBUTE_07,\n");
        sql.append("       ATTRIBUTE_08 = :ATTRIBUTE_08,\n");
        sql.append("       ATTRIBUTE_09 = :ATTRIBUTE_09,\n");
        sql.append("       ATTRIBUTE_10 = :ATTRIBUTE_10,\n");
        sql.append("       ATTRIBUTE_11 = :ATTRIBUTE_11,\n");
        sql.append("       ATTRIBUTE_12 = :ATTRIBUTE_12,\n");
        sql.append("       ATTRIBUTE_13 = :ATTRIBUTE_13,\n");
        sql.append("       ATTRIBUTE_14 = :ATTRIBUTE_14,\n");
        sql.append("       ATTRIBUTE_15 = :ATTRIBUTE_15,\n");
        sql.append("       COL_SHOW_FORMAT = :COL_SHOW_FORMAT,\n");
        sql.append("       COL_GENE_METHOD_RULE = :COL_GENE_METHOD_RULE,\n");
        sql.append("       COL_IS_DISPLAY = :COL_IS_DISPLAY,\n");
        sql.append("       COL_IS_COMPLEMENT = :COL_IS_COMPLEMENT,\n");
        sql.append("       COL_COMPLEMENT_NUM = :COL_COMPLEMENT_NUM,\n");
        sql.append("       COL_GENE_METHOD_RULE_TEXT = :COL_GENE_METHOD_RULE_TEXT\n");
        sql.append(" WHERE ID = :ID");
        for (Map<String,Object> cbbTabColumnsMap : cbbTabColumnsList) {
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
            PojoUtil.setSysDyncProperties(cbbTabColumnsMap, PlatformConstant.OpType.update);
            namedParameterJdbcTemplate.update(sql.toString(), cbbTabColumnsMap);
        }

    }

    public Map<String, List<Map<String, Object>>> getColNameCbbTabColCodeMapByTableId(String tableId){
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.col_name, t2.*\n");
        sql.append("from cbb_tab_columns t1, cbb_tab_col_code t2\n");
        sql.append("where t1.table_id=t2.table_id\n");
        sql.append("and t1.id = t2.column_id\n");
        sql.append("and t1.table_id='"+tableId+"'");

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
        Map<String, List<Map<String, Object>>> result = new HashMap<>();
        List<Map<String, Object>> cbbTabColCodeList;
        String colName;
        for (Map<String, Object> map : list) {
            colName = (String) map.get("COL_NAME");
            cbbTabColCodeList = result.get(colName);
            if(cbbTabColCodeList == null){
                cbbTabColCodeList = new ArrayList<>();
            }
            cbbTabColCodeList.add(map);
            result.put(colName, cbbTabColCodeList);
        }
        return result;
    }

    public Map<String,String> getColNameColIdMap(String tableId){
        StringBuilder sql = new StringBuilder();
        sql.append("select  t1.col_name, t1.id\n");
        sql.append("from cbb_tab_columns t1\n");
        sql.append("where t1.table_id='"+tableId+"'\n");
        sql.append("and t1.id not in (\n");
        sql.append("    select t2.column_id\n");
        sql.append("    from cbb_tab_col_code t2\n");
        sql.append("    where t2.table_id='"+tableId+"'\n");
        sql.append(")");

        final Map<String,String> result = new HashMap();
        jdbcTemplate.query(sql.toString(), new ResultSetExtractor(){
            @Override
            public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                while(resultSet.next()){
                    result.put(resultSet.getString("COL_NAME"), resultSet.getString("ID"));
                }
                return result;
            }
        });
        return result;
    }

    public void saveCbbTabColCodeMapList(List<Map<String, Object>> cbbTabColCodeList) throws Exception{
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO CBB_TAB_COL_CODE\n");
        sql.append("    (ID,\n");
        sql.append("     TABLE_ID,\n");
        sql.append("     COLUMN_ID,\n");
        sql.append("     CODE_ORDER,\n");
        sql.append("     LOOKUP_NAME,\n");
        sql.append("     LOOKUP_CODE,\n");
        sql.append("     CODE_IS_VALID,\n");
        sql.append("     REMARK,\n");
        sql.append("     ORG_ID,\n");
        sql.append("     DEPT_ID,\n");
        sql.append("     SYS_ID,\n");
        sql.append("     SECRET_LEVEL,\n");
        sql.append("     SYS_APPLICATION_ID,\n");
        sql.append("     LAST_UPDATE_DATE,\n");
        sql.append("     LAST_UPDATED_BY,\n");
        sql.append("     CREATION_DATE,\n");
        sql.append("     CREATED_BY,\n");
        sql.append("     LAST_UPDATE_IP,\n");
        sql.append("     VERSION,\n");
        sql.append("     ATTRIBUTE_01,\n");
        sql.append("     ATTRIBUTE_02,\n");
        sql.append("     ATTRIBUTE_03,\n");
        sql.append("     ATTRIBUTE_04,\n");
        sql.append("     ATTRIBUTE_05,\n");
        sql.append("     ATTRIBUTE_06,\n");
        sql.append("     ATTRIBUTE_07,\n");
        sql.append("     ATTRIBUTE_08,\n");
        sql.append("     ATTRIBUTE_09,\n");
        sql.append("     ATTRIBUTE_10,\n");
        sql.append("     ATTRIBUTE_11,\n");
        sql.append("     ATTRIBUTE_12,\n");
        sql.append("     ATTRIBUTE_13,\n");
        sql.append("     ATTRIBUTE_14,\n");
        sql.append("     ATTRIBUTE_15)\n");
        sql.append("VALUES\n");
        sql.append("    (:ID,\n");
        sql.append("     :TABLE_ID,\n");
        sql.append("     :COLUMN_ID,\n");
        sql.append("     :CODE_ORDER,\n");
        sql.append("     :LOOKUP_NAME,\n");
        sql.append("     :LOOKUP_CODE,\n");
        sql.append("     :CODE_IS_VALID,\n");
        sql.append("     :REMARK,\n");
        sql.append("     :ORG_ID,\n");
        sql.append("     :DEPT_ID,\n");
        sql.append("     :SYS_ID,\n");
        sql.append("     :SECRET_LEVEL,\n");
        sql.append("     :SYS_APPLICATION_ID,\n");
        sql.append("     :lastUpdateDate,\n");
        sql.append("     :lastUpdatedBy,\n");
        sql.append("     :creationDate,\n");
        sql.append("     :createdBy,\n");
        sql.append("     :lastUpdateIp,\n");
        sql.append("     :version,\n");
        sql.append("     :ATTRIBUTE_01,\n");
        sql.append("     :ATTRIBUTE_02,\n");
        sql.append("     :ATTRIBUTE_03,\n");
        sql.append("     :ATTRIBUTE_04,\n");
        sql.append("     :ATTRIBUTE_05,\n");
        sql.append("     :ATTRIBUTE_06,\n");
        sql.append("     :ATTRIBUTE_07,\n");
        sql.append("     :ATTRIBUTE_08,\n");
        sql.append("     :ATTRIBUTE_09,\n");
        sql.append("     :ATTRIBUTE_10,\n");
        sql.append("     :ATTRIBUTE_11,\n");
        sql.append("     :ATTRIBUTE_12,\n");
        sql.append("     :ATTRIBUTE_13,\n");
        sql.append("     :ATTRIBUTE_14,\n");
        sql.append("     :ATTRIBUTE_15)");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        Map<String, Object>[] param = new Map[cbbTabColCodeList.size()];
        try {
            namedParameterJdbcTemplate.batchUpdate(sql.toString(), cbbTabColCodeList.toArray(param));
        }catch (Exception e){
            e.printStackTrace();
            LOGGER.info(e.getMessage());
            throw e;
        }
    }

}
